

CREATE TABLE [dbo].[UserRoleRel](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[UserKey] [int] NOT NULL,
	[RoleKey] [int] NOT NULL,
	[Creator] [nvarchar](32) NULL,
	[CreateDate] [datetime] NULL,
	[LastChanged] [timestamp] NULL,
 CONSTRAINT [PK_USERROLEREL] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[UserRoleRel]  WITH CHECK ADD  CONSTRAINT [FK_UserRoleRel_Role] FOREIGN KEY([RoleKey])
REFERENCES [dbo].[Role] ([ID])
GO

ALTER TABLE [dbo].[UserRoleRel] CHECK CONSTRAINT [FK_UserRoleRel_Role]
GO

ALTER TABLE [dbo].[UserRoleRel]  WITH CHECK ADD  CONSTRAINT [FK_UserRoleRel_UserAccount] FOREIGN KEY([UserKey])
REFERENCES [dbo].[UserAccount] ([ID])
GO

ALTER TABLE [dbo].[UserRoleRel] CHECK CONSTRAINT [FK_UserRoleRel_UserAccount]
GO




CREATE TABLE [dbo].[UserAccount](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[UserCode] [nvarchar](32) NOT NULL,
	[UserName] [nvarchar](32) NOT NULL,
	[CompKey] [nvarchar](50) NULL,
	[EmployeeKey] [nvarchar](36) NOT NULL,
	[UserPaswd] [nvarchar](64) NOT NULL,
	[Tel] [nvarchar](25) NULL,
	[Mobile] [nvarchar](15) NULL,
	[EMail] [nvarchar](255) NULL,
	[EMail_cc] [nvarchar](255) NULL,
	[EMail_bcc] [nvarchar](255) NULL,
	[UserType] [int] NULL,
	[ValidFrom] [datetime] NULL,
	[ValidTo] [datetime] NULL,
	[DeleteFlag] [bit] NULL,
	[PasswdTip] [nvarchar](50) NULL,
	[TipAnswer] [nvarchar](50) NULL,
	[ImportTime] [nvarchar](50) NULL,
	[CreateDate] [datetime] NULL,
	[Creator] [nvarchar](20) NULL,
	[Modifier] [nvarchar](20) NULL,
	[ModiDate] [datetime] NULL,
	[LastChanged] [timestamp] NULL,
	[PassWordLastChanged] [datetime] NULL,
 CONSTRAINT [PK_USERACCOUNT] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[UserAccount] ADD  CONSTRAINT [DF_UserAccount_UserType]  DEFAULT ((0)) FOR [UserType]
GO



CREATE TABLE [dbo].[Role](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[RoleCode] [nvarchar](20) NOT NULL,
	[RoleName] [nvarchar](50) NOT NULL,
	[RoleType] [int] NULL,
	[DeleteFlag] [bit] NULL,
	[Remark] [nvarchar](255) NULL,
	[LastChangedBy] [nvarchar](50) NULL,
	[LastChanged] [timestamp] NULL,
 CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Role] ADD  CONSTRAINT [DF__Role__RoleType__40058253]  DEFAULT ((0)) FOR [RoleType]
GO




